package com.sxj.corejava.code02_jdbc;

import com.sxj.corejava.entity.Dept;
import com.sxj.corejava.entity.Emp;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 石小俊
 * @date 2023年12月2023/12/2日 10:06
 */
public class Test08_多表查询 {

    public static void main(String[] args) {
//        List<Emp> emps = selectAllEmp();
//        for(Emp emp : emps){
//            System.out.println(emp);
//        }

        Dept dept = selectByDeptId(1);
        System.out.println(dept);
    }

    /**
     * 多对一查询
     * 查询所有员工以及其所属部门信息
     * @return
     */
    public static List<Emp> selectAllEmp(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Emp> emps = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select e.id,e.name,e.salary,d.id 'd.id',d.name 'd.name',remark ")
                    .append(" from t_emp e ")
                    .append(" left join t_dept d ")
                    .append("   on e.dept_id = d.id ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                // 此时映射时,需要映射两种对象:员工对象与部门对象
                // 且部门对象作为员工对象的属性存在
                // 映射员工对象
                Emp emp = new Emp();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSalary(rs.getDouble("salary"));

                // 映射部门对象
                Dept dept = new Dept();
                dept.setId(rs.getInt("d.id"));
                dept.setName(rs.getString("d.name"));
                dept.setRemark(rs.getString("remark"));
                // 将映射好的部门对象交给员工对象的对象属性
                emp.setDept(dept);
                // 每映射一行数据,生成一个Emp对象,添加到对应的集合中
                emps.add(emp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return emps;
    }

    /**
     * 一对多查询
     * 根据主键查询部门以及部门下的所有员工信息
     * @param id
     * @return
     */
    public static Dept selectByDeptId(Integer id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Dept dept = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select d.id,d.name,d.remark,e.id 'e.id',e.name 'e.name',e.salary ")
                    .append(" from t_dept d ")
                    .append(" left join t_emp e ")
                    .append("   on d.id = e.dept_id ")
                    .append(" where d.id = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                // 一对多映射
                // 一方数据的映射只映射一次
                // 映射前判断是否已经映射过了
                // 只有尚未映射时才需要映射
                if(dept == null) {
                    dept = new Dept();
                    dept.setId(rs.getInt("id"));
                    dept.setName(rs.getString("name"));
                    dept.setRemark(rs.getString("remark"));
                }
                // 多方数据的映射映射多次
                // 每次映射完成之后将映射的对象添加到一方数据的集合属性中
                Emp emp = new Emp();
                emp.setId(rs.getInt("e.id"));
                emp.setName(rs.getString("e.name"));
                emp.setSalary(rs.getDouble("salary"));
                dept.addEmp(emp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("未找到驱动");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("连接数据库失败");
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("释放连接出错");
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return dept;
    }

}
